import datetime
import os
import shutil

import openpyxl
from openpyxl.styles import PatternFill, Font
from openpyxl.utils import get_column_letter

from  import (today_str1, today_str2, yesterday_str1,
                       vlookup,
                       lj_qgmxb_y, lj_wlxxb, lj_expected_procurement, lj_gdmx, lj_cp, detect_date, lj_pjmx_tocg, lj_pjmx
                       )


def ifin(lookup_value, table_array):  # 如果存在则为真
    bool1: bool = True
    for row in table_array:
        if str(row[0].value) == str(lookup_value):
            bool1 = False
    return bool1


wb_wlxxb = openpyxl.load_workbook(lj_wlxxb)
ws_wlxxb = wb_wlxxb["物料信息表" + today_str1]

wb_expected_procurement = openpyxl.load_workbook(lj_expected_procurement)
ws_expected_procurement = wb_expected_procurement.worksheets[0]

wb_qgmxb_today = openpyxl.load_workbook(lj_qgmxb_y)
ws_qgmxb_today = wb_qgmxb_today.worksheets[0]

wb_gdmx = openpyxl.load_workbook(lj_gdmx)
ws_gdmx = wb_gdmx.worksheets[0]

wb_cp = openpyxl.load_workbook(lj_cp)
ws_cp_OA = wb_cp["OA完结调控面板"]
ws_cp_sn = wb_cp["配件品号"]
ws_cp_pjqg = wb_cp["配件请购"]

wb = openpyxl.Workbook()
ws = wb.worksheets[0]

dict_qgmxb = {"备料原因": None,
              "品号": None,
              }
for i in range(1, ws_qgmxb_today.max_column + 1):
    if ws_qgmxb_today.cell(1, i).value in dict_qgmxb.keys():
        dict_qgmxb[ws_qgmxb_today.cell(1, i).value] = i

table_array_cp_sn = list(ws_cp_sn["A1":"A" + str(ws_cp_sn.max_row)])
for i in range(2, ws_qgmxb_today.max_row + 1):
    if ("线缆安全库存" in ws_qgmxb_today.cell(i, dict_qgmxb[
        "备料原因"]).value or "销售紧急需求备料" in ws_qgmxb_today.cell(
        i, dict_qgmxb["备料原因"]).value) and \
            ws_qgmxb_today.cell(i, dict_qgmxb["品号"]).value[0:5] != "10309" and \
            ws_qgmxb_today.cell(i, dict_qgmxb["品号"]).value[0:5] != "10310" and \
            ws_qgmxb_today.cell(i, dict_qgmxb["品号"]).value[0:5] != "10101":
        if ifin(ws_qgmxb_today.cell(i, dict_qgmxb["品号"]).value, table_array_cp_sn):
            ws_cp_sn.cell(ws_cp_sn.max_row + 1, 1).value = ws_qgmxb_today.cell(i, dict_qgmxb["品号"]).value
list_sn = []
for i in range(1, ws_cp_sn.max_row + 1):
    list_sn.append(ws_cp_sn.cell(i, 1).value[0:12])

try:
    wb1 = openpyxl.load_workbook(
        r"\\192.168.70.101\06采购\001.采购共享-生产物料滚动需求计划（可编辑）\02.线缆需求及在途一览表2024\镜头、光源、配件备货--货期一览表to采购" + yesterday_str1 + ".xlsx")
except FileNotFoundError:
    wb1 = openpyxl.load_workbook(
        r"\\192.168.70.101\06采购\001.采购共享-生产物料滚动需求计划（可编辑）\02.线缆需求及在途一览表2024\镜头、光源、配件备货--货期一览表to采购" + today_str1 + ".xlsx")
ws1 = wb1.worksheets[0]
for i in range(3, ws1.max_row + 1):
    for j in range(2, ws_cp_pjqg.max_row + 1):
        if ws1.cell(i, 3).value == ws_cp_pjqg.cell(j, 1).value:
            ws_cp_pjqg.cell(j, 14).value = ws1.cell(i, 13).value
# 更新
table_array_cp_qgwj = list(ws_cp_pjqg["A1":"N" + str(ws_cp_pjqg.max_row)])
ws_qgmxb_today.insert_cols(3)
for i in range(2, ws_qgmxb_today.max_row + 1):
    ws_qgmxb_today.cell(i, 3).value = ws_qgmxb_today.cell(i, 1).value + " -" + ws_qgmxb_today.cell(i, 2).value

a = ws_cp_pjqg.max_row + 1
for i in range(2, ws_qgmxb_today.max_row):
    if (int(ws_qgmxb_today.cell(i, 1).value[5:15]) > 2023040001 and
            ws_qgmxb_today.cell(i, 4).value in list_sn and
            ifin(ws_qgmxb_today.cell(i, 3).value, table_array_cp_qgwj)):
        if "线缆安全库存" in ws_qgmxb_today.cell(i, 11).value or \
                "销售紧急需求备料" in ws_qgmxb_today.cell(i, 11).value:
            ws_cp_pjqg.cell(a, 1).value = ws_qgmxb_today.cell(i, 3).value
            ws_cp_pjqg.cell(a, 2).value = ws_qgmxb_today.cell(i, 10).value
            ws_cp_pjqg.cell(a, 3).value = ws_qgmxb_today.cell(i, 4).value
            ws_cp_pjqg.cell(a, 4).value = ws_qgmxb_today.cell(i, 5).value
            ws_cp_pjqg.cell(a, 5).value = ws_qgmxb_today.cell(i, 6).value
            ws_cp_pjqg.cell(a, 6).value = ws_qgmxb_today.cell(i, 9).value
            ws_cp_pjqg.cell(a, 7).value = ws_qgmxb_today.cell(i, 8).value
            ws_cp_pjqg.cell(a, 11).value = ws_qgmxb_today.cell(i, 11).value
            ws_cp_pjqg.cell(a, 9).value = "采购审核中"
            a += 1

a = ws_cp_OA.max_row + 1
for i in range(2, ws_cp_pjqg.max_row + 1):
    table_array_cp_OA = list(ws_cp_OA["A2":"A" + str(ws_cp_OA.max_row)])
    if ifin(ws_cp_pjqg.cell(i, 2).value, table_array_cp_OA):
        ws_cp_OA.cell(a, 1).value = ws_cp_pjqg.cell(i, 2).value
        a += 1

# 在途线缆一览表
table_array_qgmxb = list(ws_qgmxb_today["C2":"M" + str(ws_qgmxb_today.max_row)])
table_array_cp_OA = list(ws_cp_OA["A2":"B" + str(ws_cp_OA.max_row)])

a = 2
for i in range(2, ws_expected_procurement.max_row + 1):
    if ws_expected_procurement.cell(i, 2).value in list_sn:
        # 数据转移
        ws.cell(a, 3).value = ws_expected_procurement.cell(i, 14).value  # 请购单号
        ws.cell(a, 11).value = detect_date(vlookup(ws.cell(a, 3).value, table_array_cp_qgwj, 7),
                                           1)  # 需求日期
        ws.cell(a, 11).number_format = 'yyyy/mm/dd'
        # 合并OA流水号、OA发起人
        ws.cell(a, 1).value = vlookup(ws.cell(a, 3).value, table_array_qgmxb, 8)
        ws.cell(a, 2).value = vlookup(ws.cell(a, 1).value, table_array_cp_OA, 2)
        ws.cell(a, 4).value = ws_expected_procurement.cell(i, 2).value
        ws.cell(a, 5).value = ws_expected_procurement.cell(i, 3).value
        ws.cell(a, 6).value = ws_expected_procurement.cell(i, 4).value
        ws.cell(a, 7).value = ws_expected_procurement.cell(i, 5).value
        ws.cell(a, 8).value = ws_expected_procurement.cell(i, 7).value
        ws.cell(a, 9).value = ws_expected_procurement.cell(i, 6).value
        ws.cell(a, 10).value = ws_expected_procurement.cell(i, 8).value
        ws.cell(a, 12).value = detect_date(ws_expected_procurement.cell(i, 1).value, 1)
        ws.cell(a, 12).number_format = 'yyyy/mm/dd'
        ws.cell(a, 15).value = ws_expected_procurement.cell(i, 20).value  # 供应商
        ws.cell(a, 13).value = vlookup(ws.cell(a, 3).value, table_array_cp_qgwj, 14)  # 导入采购反馈货期
        ws.cell(a, 13).number_format = 'yyyy/mm/dd'
        ws.cell(a, 16).value = (ws_expected_procurement.cell(i, 17).value + "-"
                                + ws_expected_procurement.cell(i, 18).value + "-" + ws_expected_procurement.cell(i,
                                                                                                                 19).value)
        if ws.cell(a, 8).value == 0:
            ws.cell(a, 14).value = "在检"
        if ws.cell(a, 13).value is not None:
            ws.cell(a, 12).font = Font(strike=True)
        a += 1

table_array_xlztmx = list(ws_expected_procurement["N1":"S" + str(ws_expected_procurement.max_row)])
table_array_xlmx_zt = list(ws["C2":"C" + str(ws.max_row)])
for i in range(2, ws_cp_pjqg.max_row + 1):
    if ws_cp_pjqg.cell(i, 9).value == "交货中" and ifin(ws_cp_pjqg.cell(i, 1).value, table_array_xlmx_zt):
        ws_cp_pjqg.cell(i, 9).value = "完结"
        ws_cp_pjqg.cell(i, 10).value = today_str2
    elif ws_cp_pjqg.cell(i, 9).value == "采购审核中" and not ifin(ws_cp_pjqg.cell(i, 1).value, table_array_xlmx_zt):
        ws_cp_pjqg.cell(i, 9).value = "交货中"
        ws_cp_pjqg.cell(i, 12).value = (vlookup(ws_cp_pjqg.cell(i, 1).value, table_array_xlztmx, 4)
                                        + "-" + vlookup(ws_cp_pjqg.cell(i, 1).value, table_array_xlztmx, 5)
                                        + "-" + vlookup(ws_cp_pjqg.cell(i, 1).value, table_array_xlztmx, 6))
        for j in range(2, ws_expected_procurement.max_row + 1):  # 预交货日期
            if ws_expected_procurement.cell(j, 14).value == ws_cp_pjqg.cell(i, 1).value:
                ws_cp_pjqg.cell(i, 13).value = detect_date(ws_expected_procurement.cell(j, 1).value, 1)
                ws_cp_pjqg.cell(i, 13).number_format = 'yyyy/mm/dd'

a = ws.max_row + 1
for i in range(2, ws_cp_pjqg.max_row + 1):
    if ws_cp_pjqg.cell(i, 9).value == "采购审核中":
        ws.cell(a, 1).value = ws_cp_pjqg.cell(i, 2).value
        ws.cell(a, 3).value = ws_cp_pjqg.cell(i, 1).value
        ws.cell(a, 4).value = ws_cp_pjqg.cell(i, 3).value
        ws.cell(a, 5).value = ws_cp_pjqg.cell(i, 4).value
        ws.cell(a, 6).value = ws_cp_pjqg.cell(i, 5).value
        ws.cell(a, 7).value = ws_cp_pjqg.cell(i, 6).value
        ws.cell(a, 8).value = ws.cell(a, 7).value
        ws.cell(a, 9).value = 0
        ws.cell(a, 10).value = 0
        ws.cell(a, 11).value = detect_date(ws_cp_pjqg.cell(i, 7).value, 1)
        ws.cell(a, 11).number_format = 'yyyy/mm/dd'
        ws.cell(a, 14).value = "采购审核中"
        ws.cell(a, 2).value = vlookup(ws.cell(a, 1).value, table_array_cp_OA, 2)
        a += 1

font_red = Font(color="FF0000")
fill = PatternFill(patternType="solid", fgColor="B8CCE4")
list1 = ["OA流水号", "发起人", "请购单号", "品号", "品名", "规格", "需求数量", "未交货数量", "已入库数量", "在检数量",
         "需求日期", "预交货日期", "货期变动", "当前状态", "供应商", "采购单号"]
list2 = [14, 7, 16.5, 14, 26, 28, 10, 10, 10, 10, 12, 12, 20, 10, 30, 20]
for i in range(1, 17):
    ws.cell(1, i).value = list1[i - 1]
    ws.cell(1, i).fill = fill
    ws.column_dimensions[get_column_letter(i)].width = list2[i - 1]
ws.title = "在途配件一览表"
ws.insert_rows(1)
ws.cell(1, 1).value = "注：若采购反馈货期不能满足需求，请及时反馈以便调整货期！"
ws.cell(1, 1).font = font_red
ws.cell(1, 12).value = "货期反馈时间：" + str(datetime.datetime.now())
ws.freeze_panes = 'A3'
range_active = "A2:N" + str(ws.max_row)
ws.auto_filter.ref = range_active

# 数据手动补偿
for i in range(2, ws.max_row + 1):
    if ws.cell(i, 3).value is not None:
        if ws.cell(i, 3).value[:15] == "3102-2023120006" \
                or ws.cell(i, 3).value[:15] == "3104-2024010003" \
                or ws.cell(i, 3).value[:15] == "3104-2024060008":
            ws.cell(i, 2).value = "许春雨"

# 在制线缆一览表
ws_zz = wb.create_sheet("在制配件一览表")
ws_gdmx.insert_cols(3)
a = 2
for i in range(2, ws_gdmx.max_row + 1):
    ws_gdmx.cell(i, 3).value = ws_gdmx.cell(i, 1).value + "-" + ws_gdmx.cell(i, 2).value
    if ws_gdmx.cell(i, 4).value in list_sn and \
            ws_gdmx.cell(i, 14).value != "已完工" and \
            ws_gdmx.cell(i, 14).value != "指定完工" and \
            ws_gdmx.cell(i, 3).value != ws_zz.cell(a - 1, 1).value:
        ws_zz.cell(a, 1).value = ws_gdmx.cell(i, 3).value
        ws_zz.cell(a, 2).value = ws_gdmx.cell(i, 4).value
        ws_zz.cell(a, 3).value = ws_gdmx.cell(i, 5).value
        ws_zz.cell(a, 4).value = ws_gdmx.cell(i, 6).value
        ws_zz.cell(a, 5).value = ws_gdmx.cell(i, 8).value
        ws_zz.cell(a, 6).value = ws_gdmx.cell(i, 10).value
        ws_zz.cell(a, 7).value = ws_gdmx.cell(i, 14).value
        if ws_zz.cell(a, 1).value[0:4] == "5102":
            ws_zz.cell(a, 8).value = "委外改制工单"
        a += 1
list1 = ["工单单号", "品号", "品名", "规格", "预计产量", "已生产数量", "当前状态", "备注"]
list2 = [16.5, 14, 26, 28, 10, 10, 10, 15]
for i in range(1, 9):
    ws_zz.cell(1, i).value = list1[i - 1]
    ws_zz.cell(1, i).fill = fill
    ws_zz.column_dimensions[get_column_letter(i)].width = list2[i - 1]
ws_zz.freeze_panes = 'A2'
range_active = "A1:G" + str(ws_zz.max_row)
ws_zz.auto_filter.ref = range_active

# 线缆库存一览表
ws_xlmx_kc = wb.create_sheet("配件库存一览表")
a = 3
for i in range(3, ws_wlxxb.max_row + 1):
    if str(ws_wlxxb.cell(i, 1).value) in list_sn:
        ws_xlmx_kc.cell(a, 1).value = str(ws_wlxxb.cell(i, 1).value)
        ws_xlmx_kc.cell(a, 2).value = ws_wlxxb.cell(i, 2).value
        ws_xlmx_kc.cell(a, 3).value = ws_wlxxb.cell(i, 3).value
        ws_xlmx_kc.cell(a, 4).value = ws_wlxxb.cell(i, 14).value
        ws_xlmx_kc.cell(a, 5).value = ws_wlxxb.cell(i, 15).value - ws_wlxxb.cell(i, 16).value
        ws_xlmx_kc.cell(a, 6).value = ws_wlxxb.cell(i, 16).value
        ws_xlmx_kc.cell(a, 7).value = ws_wlxxb.cell(i, 17).value
        ws_xlmx_kc.cell(a, 8).value = ws_wlxxb.cell(i, 19).value
        a += 1

ws_xlmx_kc.cell(1, 1).value = "物料信息结存时间" + today_str1 + " 8:30"
list1 = ["品号", "品名", "规格", "可用数量", "在途数量", "在检数量", "已请未购数量", "固定前置天数"]
list2 = [14, 26, 28, 10, 10, 10, 10, 13]
for i in range(1, 9):
    ws_xlmx_kc.cell(2, i).value = list1[i - 1]
    ws_xlmx_kc.cell(2, i).fill = fill
    ws_xlmx_kc.column_dimensions[get_column_letter(i)].width = list2[i - 1]
ws_xlmx_kc.freeze_panes = 'A3'
range_active = "A2:H" + str(ws_xlmx_kc.max_row)
ws_xlmx_kc.auto_filter.ref = range_active

wb.save(lj_pjmx_tocg)
ws.delete_cols(15, 2)
wb.save(lj_pjmx)
wb_cp.save(lj_cp)

shutil.copy(lj_pjmx, r"\\192.168.70.101\01市场销售部\01市场销售公用")
shutil.copy(lj_pjmx_tocg,
            r"\\192.168.70.101\06采购\001.采购共享-生产物料滚动需求计划（可编辑）\02.线缆需求及在途一览表2024")
shutil.copy(lj_pjmx_tocg, r"\\192.168.70.101\09生产部\07.生产管理\记录表单-模板\来料检 IK-MC(QD)-MF(RF)-0XX")

try:
    os.remove(r"\\192.168.70.101\01市场销售部\01市场销售公用\镜头、光源、配件备货--货期一览表" + yesterday_str1 + ".xlsx")
except FileNotFoundError:
    print("文件不存在-销售-xlmx")
except PermissionError:
    print("文件已打开-销售-xlmx")
try:
    os.remove(
        r"\\192.168.70.101\06采购\001.采购共享-生产物料滚动需求计划（可编辑）\02.线缆需求及在途一览表2024\镜头、光源、配件备货--货期一览表to采购" + yesterday_str1 + ".xlsx")
except FileNotFoundError:
    print("文件不存在-采购-xlmx")
try:
    os.remove(
        r"\\192.168.70.101\09生产部\07.生产管理\记录表单-模板\来料检 IK-MC(QD)-MF(RF)-0XX\镜头、光源、配件备货--货期一览表to采购" + yesterday_str1 + ".xlsx")
except FileNotFoundError:
    print("文件不存在-检验-xlmx")

print(today_str1 + ":pjmx succeed!")
